# -*- coding: utf-8 -*-
'''
@author: olivia.dou
Created on: 2019/10/22 14:24
desc:
'''
from util.db_util import execute_query
import base.globalvars as glo
import random,logging,traceback

def get_random_label(label_dict):
    # 随机选取一条记录https://www.cnblogs.com/my_life/articles/7827233.html
    # sql = """SELECT DISTINCT %s,%s FROM %s
    #         WHERE id >= (SELECT floor( RAND() * ((SELECT MAX(id) FROM %s)-(SELECT MIN(id) FROM %s))
    #         + (SELECT MIN(id) FROM %s))) ORDER BY id LIMIT 1;"""%(label_dict['label_code'],label_dict['label_name'],label_dict['table_name'])
    sql = """SELECT DISTINCT %s,%s FROM %s AS t1 JOIN (SELECT ROUND(RAND() *
            ((SELECT MAX(id) FROM %s)-(SELECT MIN(id) FROM %s))+(SELECT MIN(id) FROM %s)) AS id) AS t2
            WHERE t1.id >= t2.id
            ORDER BY t1.id LIMIT 1"""%((label_dict['label_code_field'],label_dict['label_name_field']) + tuple([label_dict['table_name']]*4))
    res = execute_query(sql)
    return res[0]

def get_random_event():
    event_code, event_name = get_random_label({"label_code_field": "event_code", "label_name_field": "name", "table_name": "dictdb.nlp_dict_event"})
    glo.set_value("event_code",event_code)
    glo.set_value("event_name",event_name)

def get_random_industry():
    industry_code,industry_name=get_random_label({"label_code_field": "samcd", "label_name_field": "name", "table_name": "dictdb.t_dict_industry"})
    glo.set_value("industry_code",industry_code)
    glo.set_value("industry_name",industry_name)

def get_random_product():
    product_code,product_name=get_random_label({"label_code_field": "samcd", "label_name_field": "name", "table_name": "dictdb.t_dict_product"})
    glo.set_value("product_code",product_code)
    glo.set_value("product_name",product_name)

def get_random_concept():
    concept_code,concept_name=get_random_label({"label_code_field": "concept_code", "label_name_field": "name", "table_name": "dictdb.nlp_dict_concept_base"})
    glo.set_value("concept_code",concept_code)
    glo.set_value("concept_name",concept_name)

def get_random_company():
    company_code,company_name=get_random_label({"label_code_field": "pcode", "label_name_field": "name", "table_name": "dictdb.famous_people"})
    glo.set_value("company_code", company_code)
    glo.set_value("company_name", company_name)

def get_random_people():
    people_code,people_name=get_random_label({"label_code_field": "pcode", "label_name_field": "name", "table_name": "dictdb.famous_people"})
    glo.set_value("people_code", people_code)
    glo.set_value("people_name", people_name)

def get_random_region():
    try:
        res = execute_query("SELECT * FROM dictdb.t_dict_region WHERE level=4 AND valid=1")
        results = []
        for i in range(len(res)):
            code=res[i][2]+','+res[i][4]
            results.append(code)
        result=random.sample(results,1)
        return result[0]
    except Exception as e:
        logging.error("Retrieve random region code failed!")
        return None


def get_supply_chain_labels(param):
    try:
        res = execute_query(
            "select a.event_code,b.name,b.word from dictdb.nlp_dict_event_application a "
            "join dictdb.nlp_news_eng_label b "
            "on a.event_code = b.code "
            "where a.stat =2 and a.event_app_code = 'GYL999' ")

        results = []
        for i in range(len(res)):
            if param == "event_code":
                event_name = res[i][0]
            elif param == "event_name":
                event_name = res[i][1]
            elif param == "event_nameEn":
                event_name = res[i][2]
            results.append(event_name)
        return results
    except Exception as e:
        logging.error("Retrieve supply chain labels failed!" )
        logging.error(e)
        traceback.print_exc()
        return None

